package com.gosling.cloudspace.tools;

import cn.hutool.core.collection.CollectionUtil;
import com.gosling.cloudspace.common.entity.WhereSqlEntity;
import com.gosling.cloudspace.common.exception.CommonException;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedHashSet;

/**
 * Sql工具类
 *
 * @author lihonglin
 * @date 2022/5/31 9:25
 */
public class SqlTool {
    protected static final Logger logger = LoggerFactory.getLogger(SqlTool.class);

    public static void main(String[] args) {
        long start = System.currentTimeMillis();
        String tableName = "MOBAOPAY_AUDIT.BAS_MERCHANT_INFO";
        String resource = "C:\\Users\\Administrator\\Desktop\\工作簿2.xlsx";
        String target = "C:\\Users\\Administrator\\Desktop\\工作簿2.sql";
        //where条件需要的字段 ACCT_STAT
        LinkedHashSet<String> whereFields = new LinkedHashSet<>();
        whereFields.add("CUSTOMER_NO");
//        whereFields.add("PROD_FUNC");
//        whereFields.add("BUSS_TP");
        //时间格式需要toDate的字段
        LinkedHashSet<String> toDateFields = new LinkedHashSet<>();
//        toDateFields.add("TRADE_DATE");
        //时间格式需要toChar的字段
        LinkedHashSet<String> toCharFields = new LinkedHashSet<>();
        //toCharFields.add("TRADE_DATE");
        WhereSqlEntity whereSqlEntity = new WhereSqlEntity();
        whereSqlEntity.setInsert(false);
        whereSqlEntity.setResource(resource);
        whereSqlEntity.setTableName(tableName);
        whereSqlEntity.setTarget(target);
        whereSqlEntity.setWhereFields(whereFields);
        whereSqlEntity.setToDateFields(toDateFields);
        whereSqlEntity.setToCharFields(toCharFields);
        //执行体
        executor(whereSqlEntity);
        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start) + "毫秒");
    }

    /***
     * 生成SQL
     *
     * @param whereSqlEntity
     * @return
     */
    public static void executor(WhereSqlEntity whereSqlEntity) {
        logger.info("入参:{}", whereSqlEntity.toString());
        LinkedHashSet<String> whereFields = whereSqlEntity.getWhereFields();
        LinkedHashSet<String> toDateFields = whereSqlEntity.getToDateFields();
        LinkedHashSet<String> toCharFields = whereSqlEntity.getToCharFields();
        String resource = whereSqlEntity.getResource();
        boolean isInsert = whereSqlEntity.isInsert();
        String tableName = whereSqlEntity.getTableName();
        if (StringUtils.isEmpty(tableName)) {
            throw new CommonException("表名不能为空！！");
        }
        if (!isInsert && CollectionUtil.isEmpty(whereFields)) {
            throw new CommonException("更新时条件依据不能为空！！");
        }
        InputStream in = null;
        FileOutputStream out = null;
        try {
            MultipartFile resourceFile = whereSqlEntity.getResourceFile();
            in = StringUtils.isEmpty(resource) ? resourceFile.getInputStream() : new FileInputStream(resource);
            out = new FileOutputStream(whereSqlEntity.getTarget());
            Sheet sheet0 = getSheetZero(in);
            LinkedHashSet<String> fieldSet = new LinkedHashSet<>();
            if (CollectionUtil.isEmpty(sheet0)) {
                throw new CommonException("sheet1页无数据！！");
            }
            //遍历所有的行
            for (Row row : sheet0) {
                //设置更新或者插入的表名
                StringBuilder sqlBuilder = sqlBefore(tableName, isInsert);
                //拼接SQL
                if (0 == row.getRowNum()) {
                    //表头字段设置
                    setHeader(fieldSet, row);
                } else {
                    //更新操作
                    if (!isInsert) {
                        //SET拼接
                        setUpdateSet(whereFields, fieldSet, toDateFields, toCharFields, row, sqlBuilder);
                        //条件拼接
                        setWhereSet(whereFields, fieldSet, row, sqlBuilder);
                    } else {
                        final String insertBefore = insertBefore(fieldSet);
                        //插入操作-固定字段拼接
                        sqlBuilder.append(insertBefore);
                        //字段对应值拼接
                        insertValues(fieldSet, row, sqlBuilder);
                    }
                    String xData = sqlBuilder.toString();
                    System.out.println(xData);
                    logger.info(xData);
                    out.write(xData.getBytes());
                    out.write("\n".getBytes());
                }
            }
        } catch (IOException ioE) {
            ioE.printStackTrace();
        } finally {
            IOTool.closeQuietly(out);
            IOUtils.closeQuietly(in);
        }
    }

    private static Sheet getSheetZero(InputStream in) {
        Workbook wk = StreamingReader.builder()
                //缓存到内存中的行数，默认是10
                .rowCacheSize(100)
                //读取资源时，缓存到内存的字节大小，默认是1024
                .bufferSize(4096)
                //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
                .open(in);
        return wk.getSheetAt(0);
    }

    /**
     * 设置更新或者插入的表名
     *
     * @param tableName
     * @param isInsert
     * @return
     */
    private static StringBuilder sqlBefore(String tableName, boolean isInsert) {
        StringBuilder sqlBuilder = new StringBuilder(isInsert ? "INSERT INTO" : "UPDATE");
        sqlBuilder.append(" ");
        sqlBuilder.append(tableName);
        sqlBuilder.append(" ");
        return sqlBuilder;
    }

    /***
     * 表头字段设置
     *
     * @param fieldSet
     * @param row
     */
    private static void setHeader(LinkedHashSet<String> fieldSet, Row row) {
        for (Cell cell : row) {
            String cellValue = cell.getStringCellValue();
            String cellField = cellValue.trim();
            fieldSet.add(cellField);
        }
    }

    /***
     * 插入时的字段对应值
     *
     * @param fieldSet
     * @param row
     * @param sqlBuilder
     */
    private static void insertValues(LinkedHashSet<String> fieldSet, Row row, StringBuilder sqlBuilder) {
        for (Cell cell : row) {
            for (int i = 0; i < fieldSet.size(); i++) {
                if (i == cell.getColumnIndex()) {
                    sqlBuilder.append("'");
                    sqlBuilder.append(cell.getStringCellValue());
                    sqlBuilder.append("'");
                    if (i != fieldSet.size() - 1) {
                        sqlBuilder.append(",");
                    } else {
                        sqlBuilder.append(");");
                    }
                }
            }
        }
    }

    /***
     * 插入字段设置
     *
     * @param fieldSet
     * @return
     */
    private static String insertBefore(LinkedHashSet<String> fieldSet) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("(");
        //字段单独存储
        int count = 0;
        for (String field : fieldSet) {
            sqlBuilder.append(field);
            if (count != fieldSet.size() - 1) {
                sqlBuilder.append(", ");
            }
            count++;
        }
        sqlBuilder.append(")");
        sqlBuilder.append(" ");
        sqlBuilder.append("VALUES");
        sqlBuilder.append(" ");
        sqlBuilder.append("(");
        return sqlBuilder.toString();
    }

    /***
     * 条件拼接
     *
     * @param whereFields
     * @param fieldSet
     * @param row
     * @param sqlBuilder
     */
    private static void setWhereSet(LinkedHashSet<String> whereFields, LinkedHashSet<String> fieldSet, Row row, StringBuilder sqlBuilder) {
        sqlBuilder.append(" ");
        sqlBuilder.append("WHERE");
        for (Cell cell : row) {
            Iterator<String> iterator = fieldSet.iterator();
            for (int i = 0; i < fieldSet.size(); i++) {
                String field = iterator.next();
                if (whereFields.contains(field)) {
                    //条件值设置
                    if (i == cell.getColumnIndex()) {
                        sqlBuilder.append(" ");
                        sqlBuilder.append(field);
                        sqlBuilder.append(" ");
                        sqlBuilder.append("=");
                        sqlBuilder.append("'");
                        sqlBuilder.append(cell.getStringCellValue());
                        sqlBuilder.append("'");
                        //如果更新字段只有一个且当前字段序号不为最后一个
                        if (i != fieldSet.size() - 1 && whereFields.size() != 1) {
                            sqlBuilder.append(" ");
                            sqlBuilder.append("AND");
                        }
                    }
                }
            }
        }
        sqlBuilder.append(";");
    }

    /***
     * SET拼接
     *
     * @param whereFields
     * @param fieldSet
     * @param row
     * @param sqlBuilder
     */
    private static void setUpdateSet(LinkedHashSet<String> whereFields, LinkedHashSet<String> fieldSet, LinkedHashSet<String> toDateFields, LinkedHashSet<String> toCharFields, Row row, StringBuilder sqlBuilder) {
        LinkedHashSet<String> setFields = CollectionTool.differenceSet2(fieldSet, whereFields);
        sqlBuilder.append("SET");
        //条件字段集合的个数
        for (Cell cell : row) {
            Iterator<String> iterator = fieldSet.iterator();
            for (int i = 0; i < fieldSet.size(); i++) {
                String field = iterator.next();
                //如果SET字段包含字段,则说明是SET值
                if (setFields.contains(field)) {
                    //SET值设置
                    if (i == cell.getColumnIndex()) {
                        sqlBuilder.append(" ");
                        sqlBuilder.append(field);
                        sqlBuilder.append(" ");
                        sqlBuilder.append("=");
                        //时间字段处理格式-to_date()和to_char()
                        if (toDateFields.contains(field)) {
                            sqlBuilder.append(" ");
                            sqlBuilder.append("to_date(");
                        } else if (toCharFields.contains(field)) {
                            sqlBuilder.append(" ");
                            sqlBuilder.append("to_char(");
                        }
                        sqlBuilder.append("'");
                        sqlBuilder.append(cell.getStringCellValue());
                        sqlBuilder.append("'");
                        //时间字段处理格式-to_date()
                        if (toDateFields.contains(field) || toCharFields.contains(field)) {
                            sqlBuilder.append(",'YYYY/MM/DD HH24:mi:ss')");
                        }
                        //如果是第一个或者是最后一个 就不用逗号分割参数
                        Iterator<String> iterator2 = setFields.iterator();
                        for (int k = 0; k < setFields.size(); k++) {
                            if (field.equals(iterator2.next())) {
                                if (setFields.size() > 1) {
                                    if (k != setFields.size() - 1) {
                                        sqlBuilder.append(",");
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
